package com.seraph.zys.report.model.exp;

import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.beanutils.PropertyUtils;
import org.mbertoli.jfep.ExpressionNode;
import org.mbertoli.jfep.ParseError;
import org.mbertoli.jfep.Parser;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.seraph.zys.dao.domain.PcPatientVisitFee;
import com.seraph.zys.util.UtilTools;

public class ReportFunctionParser extends JdbcDaoSupport{
	
	private Parser parser;
	
	private ExpressionNode root;

	private List getReportFunction(Object obj){
		List list=null;
		String sql="select id,col_name,func,func_type,sort_id from rpt_function where table_name='"+UtilTools.beanName2TableName(obj.getClass())+"' and func_type='2' and sort_id='"+getBeanPropertyValueByColName(obj,"sort_id")+"'"
		+"union select id,col_name,func,func_type,sort_id from rpt_function where table_name='"+UtilTools.beanName2TableName(obj.getClass())+"' and func_type='1'";
		list=this.getJdbcTemplate().query(sql,new RowMapper(){
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException { 
				   Func item = new Func(); 
				   item.setId(rs.getInt("id")); 
				   item.setColname(rs.getString("col_name")); 
				   item.setFunc(rs.getString("func"));
				   item.setFuncType(rs.getInt("func_type"));
				   item.setSortId(rs.getInt("sort_id"));
				   return item; 
				}
		});
		return list;
	}
	
	private String getFunctionVariableSql(int fid,Object obj){
		StringBuffer sb=new StringBuffer();
		String sql="select distinct table_name,col_name,condition_name,condition_value,condition_type from rpt_variable where func_id="+fid;
		List list=this.getJdbcTemplate().query(sql,new RowMapper(){
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException { 
				variable item = new variable(); 
				item.setTableName(rs.getString("table_name")); 
				item.setColName(rs.getString("col_name")); 
				item.setConditionName(rs.getString("condition_name"));
				item.setConditionValue(rs.getString("condition_value"));
				item.setConditionType(rs.getInt("condition_type"));
				item.setConditionType(rs.getInt("condition_type"));
				return item; 
			}
		});
		if(list!=null && list.size()>0){
			Iterator iterator=list.iterator();
			variable item=null;
			while(iterator.hasNext()){
				item=(variable)iterator.next();
				if(item.getConditionType()==1){
					sql="select '"+item.getTableName()+"."+item.getColName()+"',"+item.getColName()+" from "+item.getTableName()+" where fiscal_year="+getBeanPropertyValueByColName(obj,"fiscal_year")+" and from_area='"+getBeanPropertyValueByColName(obj,"from_area")+"' and "+item.getConditionName()+"='"+item.getConditionValue()+"'";
				}else{
					sql="select '"+item.getTableName()+"."+item.getColName()+"',"+item.getColName()+" from "+item.getTableName()+" where fiscal_year="+getBeanPropertyValueByColName(obj,"fiscal_year")+" and from_area='"+getBeanPropertyValueByColName(obj,"from_area")+"' and "+item.getConditionName()+"='"+getBeanPropertyValueByColName(obj,item.getConditionValue())+"'";
				}
				if(sb.length()>0){
					sb.append(" union "+sql);
				}else
					sb.append(sql);
				sql=null;
				item=null;
			}
		}
		if(sb.length()>0){
			return sb.toString();
		}else
			return null;
	}
	
	private Map<String,String> getFunctionVariableValue(int fid,Object obj){
		Map hm=null;
		Connection conn=null;
		try {
			conn=this.getJdbcTemplate().getDataSource().getConnection();
			String sql =getFunctionVariableSql(fid,obj);
			if(sql!=null && sql.length()>0){
				Statement stmt= conn.createStatement();
				ResultSet rs =stmt.executeQuery(sql);
				if(rs!=null){
					hm=new HashMap<String,String>();
					while(rs.next()){
						hm.put(rs.getString(1), rs.getString(2));
					}
					rs=null;
				}
				stmt=null;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(conn!=null){
				this.releaseConnection(conn);
			}			
		}
		return hm;
	}
	
	public Object setFunctionValues(Object obj){
		List list=getReportFunction(obj);
		try {
			if(list!=null && list.size()>0){
				Iterator listIterator=list.iterator();
				Func func=null;
				String funcValue=null;
				while(listIterator.hasNext()){
					func=(Func)listIterator.next();
					funcValue=getFunctionValue(func,obj);
//					PropertyUtils.setProperty(obj, UtilTools.columnName2PropertyName(func.getColname()), new Double(funcValue).longValue());
					PropertyUtils.setProperty(obj, UtilTools.columnName2PropertyName(func.getColname()), UtilTools.getValueByPropertyType(obj, UtilTools.columnName2PropertyName(func.getColname()),funcValue));
					func=null;
					funcValue=null;
				}
				
			}
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NoSuchMethodException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return obj;
	}
	
	private String getFunctionValue(Func func,Object obj) throws IllegalAccessException, InvocationTargetException, NoSuchMethodException{
		String str=null;
		if(func.getFuncType()==1){
			str=getFunctionValueByFormula(func,obj);
		}else if(func.getFuncType()==2){
			str=getFunctionValueBySql(func,obj);
		}else{
			str="0";
		}
		return str;
	}
	private String getFunctionValueBySql(Func func,Object obj){
		String str=null,sql=null;
		if(func.getFunc()!=null && !func.getFunc().trim().equals("")){
//			sql=getFinalSql(func,obj);
			try {
				sql=UtilTools.getSQLString(obj,func.getFunc());
			} catch (IllegalAccessException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (InvocationTargetException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (NoSuchMethodException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(sql!=null && !sql.trim().equals("")){
			str=getSqlFunctionValue(sql);
		}
		if(str==null || str.trim().equals("")){
			str="0";
		}
		return str;
	}
	private String getFinalSql(Func func,Object obj){
		String sql=null;
		sql=func.getFunc()+" and fiscal_year="+getBeanPropertyValueByColName(obj,"fiscal_year")+" and from_area='"+getBeanPropertyValueByColName(obj,"from_area")+"'";
		return sql;
	}
	private String getSqlFunctionValue(String sql){
//		Long sqlValue=this.getJdbcTemplate().queryForLong(sql);
		String str=(String)this.getJdbcTemplate().queryForObject(sql, String.class);
		if(str!=null){
			return str;
		}else{
			return "0";
		}
	}
	private String getFunctionValueByFormula(Func func,Object obj){
		double varValue=0d;
//		func.setVarMap(getFunctionVariableValue(func.getId(),(Integer)PropertyUtils.getProperty(obj,"fiscalYear"),(String)PropertyUtils.getProperty(obj,"fromArea")));
		func.setVarMap(getFunctionVariableValue(func.getId(),obj));
		try {
			parser = new Parser(func.getFunc());
			root = parser.getTree();
				
			for (Iterator i = parser.getParsedVariables().iterator(); i.hasNext();) {
				String name =(String)i.next();
				if(name.indexOf(".")!=-1){
					if(func.getVarMap()==null || func.getVarMap().size()<=0)
						return "0";
					if (func.getVarMap().containsKey(name))
						root.setVariable((String)name, getKeyValueOfMap(func.getVarMap(),name));
				}else{
//                	if (func.getVarMap().containsKey(name))
//                	System.out.println(PropertyUtils.getProperty(obj,UtilTools.columnName2PropertyName(name)).toString());
					root.setVariable((String)name,UtilTools.getPropertyValueStringByColumnName(obj,name));
				}
			}
		}catch (ParseError pe) {
			pe.printStackTrace();
            root = null;
        }catch (Exception e) {
			e.printStackTrace();
        }finally{
        	if (root != null)
        		varValue=root.getValue();
        	 parser = null;
	         root = null;
        }
		return String.valueOf(varValue);
	}
	private Double getKeyValueOfMap(Map map,String keyName){
		Double keyValue=null;
		String keyValueStr=(String)map.get(keyName);
		if(keyValueStr!=null && keyValueStr.trim().length()>0){
			keyValue=Double.valueOf(keyValueStr);
		}else{
			System.out.println("the value of "+keyName+"can not get from FunctionVariables,so get default value zero.");
			keyValue=Double.valueOf("0");
		}
		return keyValue;
	}
	
	private Object getBeanPropertyValueByColName(Object obj,String colName){
		Object returnObj=null;
		try {
			returnObj=PropertyUtils.getProperty(obj,UtilTools.columnName2PropertyName(colName));
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NoSuchMethodException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return returnObj;
	}
	
	public void testConn() throws SQLException, IllegalAccessException, InvocationTargetException, NoSuchMethodException{
		PcPatientVisitFee ppvf=new PcPatientVisitFee();
		ppvf.setFiscalYear(2012);
		ppvf.setFromArea("1");
		ppvf.setSortId(1);
//		ppvf.setSuspiciousMultiple(123);
		ppvf.setPatientNum(456);
		this.setFunctionValues(ppvf);
		System.out.println(ppvf.getSuspiciousFee());
	}
	
}
class Func{
	
	private int id=0;
	
	private String colname=null;
	
	private String func=null;
	
	private Map varMap=null;
	
	private int funcType=0;
	
	private int sortId=0;

	public int getSortId() {
		return sortId;
	}

	public void setSortId(int sortId) {
		this.sortId = sortId;
	}

	public int getFuncType() {
		return funcType;
	}

	public void setFuncType(int funcType) {
		this.funcType = funcType;
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getColname() {
		return colname;
	}

	public void setColname(String colname) {
		this.colname = colname;
	}

	public String getFunc() {
		return func;
	}

	public void setFunc(String func) {
		this.func = func;
	}

	public Map getVarMap() {
		return varMap;
	}

	public void setVarMap(Map varMap) {
		this.varMap = varMap;
	}
	
}

class variable{
	
	private String tableName=null;
	
	private String colName=null;
	
	private String conditionName=null; 
	
	private String conditionValue=null;
	
	private int conditionType=0;
	
	public int getConditionType() {
		return conditionType;
	}

	public void setConditionType(int conditionType) {
		this.conditionType = conditionType;
	}

	public String getTableName() {
		return tableName;
	}

	public void setTableName(String tableName) {
		this.tableName = tableName;
	}

	public String getColName() {
		return colName;
	}

	public void setColName(String colName) {
		this.colName = colName;
	}

	public String getConditionName() {
		return conditionName;
	}

	public void setConditionName(String conditionName) {
		this.conditionName = conditionName;
	}

	public String getConditionValue() {
		return conditionValue;
	}

	public void setConditionValue(String conditionValue) {
		this.conditionValue = conditionValue;
	}

}
